package com.hc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hc.entity.Incubatormv;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author LiuZhiHao
 * @since 2019-12-04
 */
public interface IncubatormvMapper extends BaseMapper<Incubatormv> {

    @Select("SELECT\n" +
            "\t\tt1.equipmentno as equipmentNo\n" +
            "\tFROM\n" +
            "\t\tmonitorequipment t1\n" +
            "\tWHERE\n" +
            "\t\tt1.equipmenttypeid = '2'\n" +
            "\tAND t1.hospitalcode = 'H0006'")
    List<Incubatormv> getIncubBydate();



    @Select("SELECT\n" +
            "\tt1.equipmentno AS equipmentNo,\n" +
            "\tt1.equipmentname AS equipmentName,\n" +
            "\tt2.currenttemperature AS currenttemperature,\n" +
            "\tt2.currenttemperature1 AS currenttemperature1,\n" +
            "\tt2.currento2 AS currento2,\n" +
            "\tt2.currentcarbondioxide AS currentcarbondioxide,t2.currenthumidity AS currenthumidity,\n" +
            "\tt2.inputdatetime,\n" +
            "\tt1.group, \n" +
            "\tt3.instrumenttypeid as instrumentTypeId\n" +
            "FROM\n" +
            "\tmonitorequipment t1\n" +
            "\tLEFT JOIN ${tableName} t2 ON t1.equipmentno = t2.equipmentno AND t2.inputdatetime BETWEEN #{startTime} AND #{endTime} \n" +
            "\tRIGHT JOIN monitorinstrument t3 on t3.equipmentno =t2.equipmentno\n" +
            "\t \n" +
            "WHERE\n" +
            "\tt1.equipmenttypeid = '2' \n" +
            "\tAND t1.hospitalcode = 'H0006' and t1.equipmentno=#{equipmentno}")
    List<Incubatormv> getIncubByDaydate(@Param("tableName") String tableName,@Param("startTime")String startTime, @Param("endTime")String endTime, @Param("equipmentno")String equipmentno);

    @Select("SELECT\n" +
            "\tt1.equipmentno AS equipmentNo,\n" +
            "\tt1.equipmentname AS equipmentName,\n" +
            "\tt2.currenttemperature AS currenttemperature,\n" +
            "\tt2.currenttemperature1 AS currenttemperature1,\n" +
            "\tt2.currento2 AS currento2,\n" +
            "\tt2.currentcarbondioxide AS currentcarbondioxide,t2.currenthumidity AS currenthumidity,\n" +
            "\tt2.inputdatetime,\n" +
            "\tt1.group, \n" +
            "\tt3.instrumenttypeid as instrumentTypeId\n" +
            "FROM\n" +
            "\tmonitorequipment t1\n" +
            "\tLEFT JOIN monitorequipmentlastdata_20200604 t2 ON t1.equipmentno = t2.equipmentno AND t2.inputdatetime BETWEEN #{startTime} AND #{endTime} \n" +
            "\tRIGHT JOIN monitorinstrument t3 on t3.equipmentno =t2.equipmentno\n" +
            "\t \n" +
            "WHERE\n" +
            "\tt1.equipmenttypeid = '2' \n" +
            "\tAND t1.hospitalcode = 'H0006' \n" +
            "\tand t1.group <>''\n")
    List<Incubatormv> getIncubBydate64(@Param("startTime")String startTime, @Param("endTime")String endTime);


    /*@Select("select time_interval,mean_value,humidity_mv,cd_mv,oxygen_mv,third_time_interval,third_temperature_mv,thirdCdMv from incubatormv \n" +
            "where equipment_name=#{equipmentname} and DATE_FORMAT(input_time, '%Y-%m-%d') = DATE_FORMAT(#{date}, '%Y-%m-%d');")*/

    @Select("select time_interval,mean_value,humidity_mv,cd_mv,oxygen_mv,third_time_interval,third_temperature_mv,equipment_name,equipment_no,third_cd_mv,instrument_type_id,third_Yq_Mv from incubatormv \n" +
            "where DATE_FORMAT(input_time,'%Y-%m-%d')=DATE_FORMAT(#{date}, '%Y-%m-%d')")
    List<Incubatormv> selectIncubday(@Param("date") String date);

    @Select("select time_interval,mean_value,humidity_mv,cd_mv,oxygen_mv,third_time_interval,third_temperature_mv,equipment_name,equipment_no,third_cd_mv,instrument_type_id,third_Yq_Mv from incubatormv \n" +
            "where DATE_FORMAT(input_time,'%Y-%m-%d')=DATE_FORMAT(#{date}, '%Y-%m-%d') limit 1")
    List<Incubatormv> selectIncubdayLimit(@Param("date") String date);

    @Select("Delete  from incubatormv \n" +
            "where DATE_FORMAT(input_time,'%Y-%m-%d')=DATE_FORMAT(#{date}, '%Y-%m-%d') ")
    void deleteIncubdayLimit(@Param("date") String date);


    //获取数据库里面所有培养箱的名字,组成一个list集合
    @Select("select equipment_name FROM incubatormv where equipment_no=#{equipment_no};")
    List<String> selectIncoubName(@Param("equipment_no") String equipment_no);

    //获取一个培养箱一个月的数据集合
    @Select("select time_interval,mean_value,humidity_mv,cd_mv,oxygen_mv,third_time_interval,third_temperature_mv,equipment_no,third_cd_mv,input_time,instrument_type_id,third_Yq_Mv from incubatormv \n" +
            "where equipment_name=#{equipment_name} and DATE_FORMAT(input_time,'%Y-%m-%d') between DATE_FORMAT(#{stardate},'%Y-%m-%d') and DATE_FORMAT(#{enddate}, '%Y-%m-%d')")
    List<Incubatormv> selectIncubmonth(@Param("equipment_name") String equipment_name , @Param("stardate")String stardate, @Param("enddate")String enddate);

    @Delete("delete from incubatormv where DATE_FORMAT(input_time,'%Y-%m-%d') between DATE_FORMAT(#{startTime},'%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d')")
    void deleteByTime(@Param("startTime") String startTime,@Param("endTime") String endTime);


    void deleteByTimeAndEno(@Param("time") String time,@Param("endTime") String endTime,@Param("collect") List<String> collect);

}
